var express = require('express');
var router = express.Router();
var mysql = require('mysql');

function createConn() {

    var conn = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: '123456',
        database: 'stumanager'
    })

    conn.connect(err => {
        if (err) console.error('连接错误' + err.stack);
        else console.log('连接成功！ID：' + conn.threadId);
    });

    return conn;

}

/* GET home page. */
router.get('/', function (req, res, next) {

    var conn = createConn();
    if (conn) {
        var sql = 'select * from student';
        conn.query(sql, (err, result) => {
            if (err) console.log('查询出错：' + err.message);
            else {
                // res.send(JSON.stringify(result));
                conn.end();
                res.render('student', {
                    title: '学生信息列表',
                    students: result
                })
            }
        });
    }
});

// 添加学生信息
router.post('/add', (req, res) => {
    var conn = createConn();
    if(conn){
        var sql = "insert into student (sNo, sName, sSex, sBirthday, class) values ('{0}', '{1}', '{2}', '{3}', '{4}')";
        sql = sql.replace('{0}', req.body.sNo);
        sql = sql.replace('{1}', req.body.sName);
        sql = sql.replace('{2}', req.body.sSex);
        sql = sql.replace('{3}', req.body.sBirthday);
        sql = sql.replace('{4}', req.body.class);

        conn.query(sql, (err, rs)=>{
            conn.end();
            if (err) {
                console.log('添加数据错误！\n' + err.message);
                res.send({
                    code: 1,
                    msg: '添加数据错误'
                })
            } else {
                res.send({
                    code: 0,
                    msg: 'OK'
                })
            }
        })
    }
});

// 删除学生信息
router.post('/del', (req, res) => {

    var conn = createConn();
    if (conn) {

        var sql = "delete from student where sNo = '" + req.body.sNo + "'";
        conn.query(sql, (err, result) => {
            conn.end();
            if (err) {
                console.log('删除数据错误！\n' + err.message);
                res.send({
                    code: 1,
                    msg: '删除数据错误'
                })
            } else {
                res.send({
                    code: 0,
                    msg: 'OK'
                })
            }
        });

    }
});

// 修改学生信息
router.post('/modify', (req, res) => {
    var conn = createConn();
    if(conn){
        var sql = "update student set sName = '{1}', sSex = '{2}', sBirthday = '{3}', class = '{4}' where sNo = '{0}'";
        sql = sql.replace('{0}', req.body.sNo);
        sql = sql.replace('{1}', req.body.sName);
        sql = sql.replace('{2}', req.body.sSex);
        sql = sql.replace('{3}', req.body.sBirthday);
        sql = sql.replace('{4}', req.body.class);

        conn.query(sql, (err, rs)=>{
            conn.end();
            if (err) {
                console.log('修改数据错误！\n' + err.message);
                res.send({
                    code: 1,
                    msg: '修改数据错误'
                })
            } else {
                res.send({
                    code: 0,
                    msg: 'OK'
                })
            }
        })
    }
});

router.post('/filter', function (req, res, next) {

    var conn = createConn();
    if (conn) {
        var sql = 'select * from student';
        if (req.body.class && req.body.class !== 'all') {
            sql += " where class = '" + req.body.class + "'";
        }
        conn.query(sql, (err, result) => {
            if (err) console.log('查询出错：' + err.message);
            else {
                // res.send(JSON.stringify(result));
                conn.end();
                // console.log(req.body.class);
                // console.log(sql);
                res.render('stuList', {
                    students: result
                })
            }
        });
    }
});


module.exports = router;
